package org.karn.example.content_provider;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBManager
{
    // the Activity or Application that is creating an object from this class.
    Context context;

    private SQLiteDatabase db; // a reference to the database manager class.
    private final String DB_NAME = "Bookmark"; // the name of our database
    private final int DB_VERSION = 1; // the version of the database

    // the names for our database columns
    private final String TABLE_NAME = "bookmark_table";
    private final String TABLE_ROW_ID = "id";
    private final String TABLE_ROW_ONE = "url";
    private final String TABLE_ROW_TWO = "favicon";
    private final String TABLE_ROW_THREE ="title";
    private final String TABLE_ROW_FOUR = "date";
    private final String TABLE_ROW_FIVE = "folder";

    
    // TODO: write the constructor and methods for this class
    public DBManager(Context context)
    {
        this.context = context;

        // create or open the database
        CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
        this.db = helper.getWritableDatabase();
    }
    
    public boolean isEmpty()
    {
        Cursor cr = db.query(
                TABLE_NAME,
                new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO, 
                        TABLE_ROW_THREE,TABLE_ROW_FOUR,TABLE_ROW_FIVE},
                        null, null, null, null, null
        );
        
        if(cr != null && cr.getCount() > 0)
        {
            cr.close();
            return false;
        }
        else
        {
            cr.close();
            return true;
        }
    }
    
    // the beginnings our SQLiteOpenHelper class
    private class CustomSQLiteOpenHelper extends SQLiteOpenHelper
    {
        
        public CustomSQLiteOpenHelper(Context context)
        {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion,
                int newVersion) {
            // TODO Auto-generated method stub

        }

        // TODO: override the constructor and other methods for the parent class
        @Override
        public void onCreate(SQLiteDatabase db)
        {
            // the SQLite query string that will create our 3 column database table.
            String newTableQueryString = 	
                "create table " +
                TABLE_NAME +
                " (" +
                TABLE_ROW_ID + " integer primary key autoincrement not null," +
                TABLE_ROW_ONE + " text," +
                TABLE_ROW_TWO + " text," +
                TABLE_ROW_THREE+ " text," +
                TABLE_ROW_FOUR + " BLOB not null," +
                TABLE_ROW_FIVE+ " text" +
                ");";

            // execute the query string to the database.
            db.execSQL(newTableQueryString);
        }
    }
    
    
    public void addRow(bookmarItem dbValue)
    {
        // this is a key value pair holder used by android's SQLite functions
        ContentValues values = new ContentValues();

        // this is how you add a value to a ContentValues object
        // we are passing in a key string and a value string each time
        values.put(TABLE_ROW_ONE, dbValue.url);
        values.put(TABLE_ROW_TWO, dbValue.favicon);
        values.put(TABLE_ROW_THREE, dbValue.title);
        values.put(TABLE_ROW_FOUR, dbValue.created);
        values.put(TABLE_ROW_FIVE, dbValue.folder);
        // ask the database object to insert the new data 
        try
        {
            db.insert(TABLE_NAME, null, values);
        }
        catch(Exception e)
        {
            Log.e("DB ERROR", e.toString()); // prints the error message to the log
            e.printStackTrace(); // prints the stack trace to the log
        }
    }
    
    
    /*
     *  We use a Long instead of an Integer because integers in SQLite are larger than integers in Java. 
     *  Collecting data from a SQLite database and storing it in a Java Integer type may result in data loss
     *
     * */
    public void deleteRow(long rowID)
    {
        // ask the database manager to delete the row of given id
        try
        {
            db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);
        }
        catch (Exception e)
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }  
    
    public void updateRow(long rowID,bookmarItem dbValue)
    {
        // this is a key value pair holder used by android's SQLite functions
        ContentValues values = new ContentValues();
        values.put(TABLE_ROW_ONE, dbValue.title);
        values.put(TABLE_ROW_TWO, dbValue.url);
        values.put(TABLE_ROW_THREE, dbValue.created);
        values.put(TABLE_ROW_FOUR, dbValue.favicon);
        values.put(TABLE_ROW_FIVE, dbValue.folder);
        // ask the database object to update the database row of given rowID
        try 
        {
            db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);
        }
        catch (Exception e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }
    }



    /**********************************************************************
     * RETRIEVING ALL ROWS FROM THE DATABASE TABLE
     * 
     * This is an example of how to retrieve all data from a database
     * table using this class.  You should edit this method to suit your
     * needs.
     * 
     * the key is automatically assigned by the database
     */
    public ArrayList<bookmarItem> getAllRowsAsArrays()
    {
        // create an ArrayList that will hold all of the data collected from
        // the database.
        ArrayList<bookmarItem> dataArrays =
            new ArrayList<bookmarItem>();

        // this is a database call that creates a "cursor" object.
        // the cursor object store the information collected from the
        // database and is used to iterate through the data.
        Cursor cursor;
        try
        {
            // ask the database object to create the cursor.
            cursor = db.query(
                    TABLE_NAME,
                    new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO, 
                            TABLE_ROW_THREE,TABLE_ROW_FOUR,TABLE_ROW_FIVE},
                            null, null, null, null, null
            );

            // move the cursor's pointer to position zero.
            cursor.moveToFirst();

            // if there is data after the current cursor position, add it
            // to the ArrayList.
            if (!cursor.isAfterLast())
            {
                do
                {
                    bookmarItem item = new bookmarItem();

                    //dataList.add(cursor.getLong(0));
                    item.url = cursor.getString(1);
                    item.favicon = cursor.getString(2);
                    item.title = cursor.getString(3);
                    item.created = cursor.getString(4);
                    item.folder = cursor.getString(5);
                    dataArrays.add(item);
                }
                // move the cursor's pointer up one position.
                while (cursor.moveToNext());
            }
        }
        catch (SQLException e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }

        // return the ArrayList that holds the data collected from
        // the database.
        return dataArrays;
    }




    /**********************************************************************
     * RETRIEVING A ROW FROM THE DATABASE TABLE
     * 
     * This is an example of how to retrieve a row from a database table
     * using this class.  You should edit this method to suit your needs.
     * 
     * @param rowID the id of the row to retrieve
     * @return an array containing the data from the row
     */
    public ArrayList<Object> getRowAsArray(long rowID)
    {
        // create an array list to store data from the database row.
        // I would recommend creating a JavaBean compliant object 
        // to store this data instead.  That way you can ensure
        // data types are correct.
        ArrayList<Object> rowArray = new ArrayList<Object>();
        Cursor cursor;

        try
        {
            // this is a database call that creates a "cursor" object.
            // the cursor object store the information collected from the
            // database and is used to iterate through the data.
            cursor = db.query
            (
                    TABLE_NAME,
                    new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO, 
                            TABLE_ROW_THREE,TABLE_ROW_FOUR,TABLE_ROW_FIVE },
                            TABLE_ROW_ID + "=" + rowID,
                            null, null, null, null, null
            );

            // move the pointer to position zero in the cursor.
            cursor.moveToFirst();

            // if there is data available after the cursor's pointer, add
            // it to the ArrayList that will be returned by the method.
            if (!cursor.isAfterLast())
            {
                do
                {
                    rowArray.add(cursor.getLong(0));
                    rowArray.add(cursor.getString(1));
                    rowArray.add(cursor.getString(2));
                    rowArray.add(cursor.getString(3));
                    rowArray.add(cursor.getString(4));
                    rowArray.add(cursor.getString(5));
                }
                while (cursor.moveToNext());
            }

            // let java know that you are through with the cursor.
            cursor.close();
        }
        catch (SQLException e) 
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }

        // return the ArrayList containing the given row from the database.
        return rowArray;
    }
    
    public ArrayList<String> getFolders()
    {
        ArrayList<String> folders = new ArrayList<String>();
        Cursor cr = db.query(
                TABLE_NAME,
                new String[]{TABLE_ROW_FIVE},
                null, null, null, null, null);
        cr.moveToFirst();
        if (!cr.isAfterLast())
        {
            do
            {
                folders.add(cr.getString(0));
            }
            while (cr.moveToNext());
        }
        
        if(cr != null)
            cr.close();
        
        return folders;
    }
}

